﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using System.Data;
using ESOL.Business.Sales;
using ESOL.Common.Entity;
using ESOL.Business.Bpt;
using ESOL.Business.Mst;
using Suzsoft.Smart.Data;
using AutoMapper;
using BPM.Base.Tools;
using BPM.Foundation.PWDB;
using ESOL.Common;

namespace ESOL.Services
{
    public class SalesService : ISalesService
    {
        public bool TestConnect()
        {
            return true;
        }

        //SalesOrder 订单
        public SalesSoEntity GetSalesSoByKey(string salesOrderId)
        {
            try
            {
                if (string.IsNullOrEmpty(salesOrderId))
                    return null;
                return SalesSoBs.GetSalesSo(new SalesSoEntity { Id = salesOrderId });
            }
            catch (Exception ex)
            {
                throw new FaultException<ProcessExecutionFault>
                    (new ProcessExecutionFault(), ex.Message);
            }
        }

        public SalesSoItemEntity GetSalesSoItemByKey(string soItemId)
        {
            if (string.IsNullOrEmpty(soItemId))
                return null;
            return DataAccess.SelectSingle<SalesSoItemEntity>(new SalesSoItemEntity { Id = soItemId });
        }

        public List<SalesSoItemEntity> GetSalesSoItemAllListBySoNo(string soNo)
        {
            if (string.IsNullOrEmpty(soNo))
                return new List<SalesSoItemEntity>();
            return DataAccess.Select<SalesSoItemEntity>(new SalesSoItemEntity { SoNo = soNo });
        }

        public SalesSoPageResult GetSalesSoAllListByCondition(SalesSoQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.customerId))
            {
                condition += " and so.CUSTOMER_ID='" + con.customerId + "'";
            }
            else if (!string.IsNullOrEmpty(con.customerName))
            {
                condition += " and so.CUSTOMER_NAME like '%" + con.customerName + "%'";
            }
            if (!string.IsNullOrEmpty(con.soNo))
            {
                condition += " and so.SO_NO='" + con.soNo + "'";
            }
            if (!string.IsNullOrEmpty(con.soUser))
            {
                condition += " and so.ORDER_PERSON='" + con.soUser + "'";
            }
            if (con.dtpSoDateFrom != null && con.dtpSoDateFrom != new DateTime(1, 1, 1))
            {
                condition += " and so.ORDER_DATE >='" + con.dtpSoDateFrom.ToString("yyyyMMdd HH:mm:ss") + "'";
            }
            if (con.dtpSoDateTo != null && con.dtpSoDateTo != new DateTime(1, 1, 1))
            {
                condition += " and so.ORDER_DATE <='" + con.dtpSoDateTo.ToString("yyyyMMdd HH:mm:ss") + "'";
            }
            if (!string.IsNullOrEmpty(con.currency))
            {
                condition += " and so.CURRENCY='" + con.currency + "'";
            }
            if (!string.IsNullOrEmpty(con.deliveryWay))
            {
                condition += " and so.TRANSPORT_TYPE='" + con.deliveryWay + "'";
            }
            if (!string.IsNullOrEmpty(con.packageType))
            {
                condition += " and so.PACKAGE_TYPE='" + con.packageType + "'";
            }
            if (con.Status != null)
            {
                condition += " and so.STATUS='" + con.Status + "'";
            }
            if (!string.IsNullOrEmpty(con.ContractModel))
            {
                condition += " and item.CONTRACT_MODEL='" + con.ContractModel + "'";
            }
            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from SALES_SO) as cnt
from SALES_SO
where id not in (
    select top ({1}*({2}-1)) id from SALES_SO order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select so.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from ({0}) so
left join BPMDB.dbo.PW_User u1 on so.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on so.CREATED_BY=u2.userNo";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select distinct so.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from SALES_SO so
left join SALES_SO_ITEM item on so.ID=item.SO_ID
left join BPMDB.dbo.PW_User u1 on so.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on so.CREATED_BY=u2.userNo
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            SalesSoPageResult result = new SalesSoPageResult();
            if (ds.Tables[0].Rows.Count == 0)
            {
                result.results = null;
                result.TotalCount = 0;
                return result;
            }
            List<SalesSo> results = new List<SalesSo>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                SalesSoEntity so = new SalesSoEntity(dr);
                Mapper.CreateMap<SalesSoEntity, SalesSo>().ForMember(d => d.selectedFlag, s => s.Ignore());
                var soDto = AutoMapper.Mapper.Map<SalesSoEntity, SalesSo>(so);
                results.Add(soDto);
            }
            result.results = results;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;
        }

        public bool UpdateSalesSo(SalesSoEntity so)
        {
            if (so == null)
                return false;
            so.UpdatedDate = DateTime.Now;
            return DataAccess.Update(so);
        }

        public bool SaveSalesSo(SalesSoSaveParam param)
        {
            if (param == null)
                return false;
            if (param.so == null)
                return false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    SalesSoEntity cur = DataAccess.SelectSingle<SalesSoEntity>(new SalesSoEntity { Id = param.so.Id });
                    if (cur == null)//新增
                    {
                        param.so.CreatedDate = DateTime.Now;
                        param.so.UpdatedDate = DateTime.Now;
                        param.so.SoNo = MstCodeRuleBs.GetCurrentOrderCode("SalesOrder");
                        foreach (var item in param.addItems)
                        {
                            item.SoNo = param.so.SoNo;
                            item.SoId = param.so.Id;
                        }
                        DataAccess.Insert(param.so, broker);
                        DataAccess.Insert(param.addItems, broker);
                        DataAccess.Insert(param.rls, broker);
                        broker.Commit();
                        return true;
                    }
                    else//修改
                    {
                        param.so.UpdatedDate = DateTime.Now;
                        foreach (var item in param.addItems)
                        {
                            item.SoNo = param.so.SoNo;
                            item.SoId = param.so.Id;
                        }
                        DataAccess.Update(param.so, broker);
                        DataAccess.Delete(param.delItems, broker);
                        DataAccess.Update(param.updatedItems, broker);
                        DataAccess.Insert(param.addItems, broker);

                        DataAccess.Delete(param.rls, broker);
                        DataAccess.Insert(param.rls, broker);
                        broker.Commit();
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }
        }

        //SalesSoItem 订单明细
        public SalesSoItemPageResult GetSalesSoItemAllListByCondition(SalesSoQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.customerId))
            {
                condition += " and so.CUSTOMER_ID='" + con.customerId + "'";
            }
            else if (!string.IsNullOrEmpty(con.customerName))
            {
                condition += " and so.CUSTOMER_NAME like '%" + con.customerName + "%'";
            }
            if (!string.IsNullOrEmpty(con.soNo))
            {
                condition += " and so.SO_NO='" + con.soNo + "'";
            }
            if (!string.IsNullOrEmpty(con.soUser))
            {
                condition += " and so.ORDER_PERSON='" + con.soUser + "'";
            }
            if (con.dtpSoDateFrom != null && con.dtpSoDateFrom != new DateTime(1, 1, 1))
            {
                condition += " and so.ORDER_DATE >='" + con.dtpSoDateFrom.ToString("yyyyMMdd HH:mm:ss") + "'";
            }
            if (con.dtpSoDateTo != null && con.dtpSoDateTo != new DateTime(1, 1, 1))
            {
                condition += " and so.ORDER_DATE <='" + con.dtpSoDateTo.ToString("yyyyMMdd HH:mm:ss") + "'";
            }
            if (con.Status != null)
            {
                condition += " and so.STATUS='" + con.Status + "'";
            }
            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from SALES_SO_ITEM) as cnt
from SALES_SO_ITEM 
where id not in (
    select top ({1}*({2}-1)) id from SALES_SO_ITEM)";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select soi.*,so.CUSTOMER_ID,so.CUSTOMER_NAME
from ({0}) soi
left join SALES_SO so on soi.SO_NO=so.SO_NO";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t )";
                string sqlSource = @"select soi.*,so.CUSTOMER_ID,so.CUSTOMER_NAME
from SALES_SO_ITEM soi
left join SALES_SO so on soi.SO_NO=so.SO_NO
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            SalesSoItemPageResult result = new SalesSoItemPageResult();
            if (ds.Tables[0].Rows.Count == 0)
            {
                result.SoItems = null;
                result.TotalCount = 0;
                return result;
            }
            List<SalesSoItem> solItems = new List<SalesSoItem>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                SalesSoItemEntity soi = new SalesSoItemEntity(dr);
                Mapper.CreateMap<SalesSoItemEntity, SalesSoItem>().ForMember(d => d.SelectedFlag, s => s.Ignore());
                var soiDto = AutoMapper.Mapper.Map<SalesSoItemEntity, SalesSoItem>(soi);
                if (dr["CUSTOMER_ID"] != null)
                {
                    soiDto.CustomerId = dr["CUSTOMER_ID"].ToString();
                }
                if (dr["CUSTOMER_NAME"] != null)
                {
                    soiDto.CustomerName = dr["CUSTOMER_NAME"].ToString();
                }
                solItems.Add(soiDto);
            }
            result.SoItems = solItems;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;
        }

        //SalesApp 申请单
        public SalesAppEntity GetSalesAppByKey(string salesAppId)
        {
            try
            {
                if (string.IsNullOrEmpty(salesAppId))
                    return null;
                return SalesAppBs.GetSalesApp(new SalesAppEntity { Id = salesAppId });
            }
            catch (Exception ex)
            {
                throw new FaultException<ProcessExecutionFault>
                    (new ProcessExecutionFault(), ex.Message);
            }
        }

        public SalesAppEntity GetSalesAppByAppNo(string salesAppNo)
        {
            try
            {
                if (string.IsNullOrEmpty(salesAppNo))
                    return null;
                return DataAccess.SelectSingle<SalesAppEntity>(new SalesAppEntity { AppNo = salesAppNo });
            }
            catch (Exception ex)
            {
                throw new FaultException<ProcessExecutionFault>
                    (new ProcessExecutionFault(), ex.Message);
            }
        }

        public DataSet GetSalesAppItemAllDsByAppId(string salesAppId)
        {
            try
            {
                if (string.IsNullOrEmpty(salesAppId))
                    return null;
                return SalesAppItemBs.GetAllDsByAppId(salesAppId);
            }
            catch (Exception ex)
            {
                throw new FaultException<ProcessExecutionFault>
                    (new ProcessExecutionFault(), ex.Message);
            }
        }

        public SalesAppPageResult GetSalesAppAllListByCondition(SalesAppQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            condition += " and app.IS_ACTIVE='" + con.isActive + "'";
            if (!string.IsNullOrEmpty(con.AppNo))
            {
                condition += " and app.APP_NO='" + con.AppNo + "'";
            }
            if (!string.IsNullOrEmpty(con.SoNo))
            {
                condition += " and app.SO_NO='" + con.SoNo + "'";
            }
            if (!string.IsNullOrEmpty(con.CustomerId))
            {
                condition += " and app.CUSTOMER_ID='" + con.CustomerId + "'";
            }
            else if (!string.IsNullOrEmpty(con.CustomerName))
            {
                condition += " and app.CUSTOMER_NAME='" + con.CustomerName + "'";
            }
            if (!string.IsNullOrEmpty(con.ContactPerson))
            {
                condition += " and app.CONTACT_PERSON='" + con.ContactPerson + "'";
            }
            if (!string.IsNullOrEmpty(con.AppPerson))
            {
                condition += " and app.APP_PERSON='" + con.AppPerson + "'";
            }
            if (!string.IsNullOrEmpty(con.PaymentType))
            {
                condition += " and app.PAYMENT_TYPE='" + con.PaymentType + "'";
            }
            if (!string.IsNullOrEmpty(con.TranType))
            {
                condition += " and app.TRASPORT_TYPE='" + con.TranType + "'";
            }
            if (!string.IsNullOrEmpty(con.DeliveryType))
            {
                condition += " and app.DELIVERY_TYPE='" + con.DeliveryType + "'";
            }
            if (!string.IsNullOrEmpty(con.ContractModel))
            {
                condition += " and item.CUSTOMER_MODEL='" + con.ContractModel + "'";
            }
            if (con.Status != null)
            {
                condition += " and app.STATUS='" + con.Status + "'";
            }
            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from SALES_APP) as cnt
from SALES_APP
where id not in (
    select top ({1}*({2}-1)) id from SALES_APP order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select app.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from ({0}) app
left join BPMDB.dbo.PW_User u1 on app.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on app.CREATED_BY=u2.userNo";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select distinct app.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from SALES_APP app
left join SALES_APP_ITEM item on app.ID=item.APP_ID
left join BPMDB.dbo.PW_User u1 on app.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on app.CREATED_BY=u2.userNo
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            SalesAppPageResult result = new SalesAppPageResult();
            if (ds.Tables[0].Rows.Count == 0)
            {
                result.results = null;
                result.TotalCount = 0;
                return result;
            }
            List<SalesAppEntity> results = new List<SalesAppEntity>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                SalesAppEntity app = new SalesAppEntity(dr);
                results.Add(app);
            }
            result.results = results;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;
        }

        public bool UpdateSalesApp(SalesAppEntity salesApp)
        {
            try
            {
                if (salesApp == null)
                    return false;
                salesApp.UpdatedDate = DateTime.Now;
                return SalesAppBs.UpdateSalesApp(salesApp);
            }
            catch (Exception ex)
            {
                throw new FaultException<ProcessExecutionFault>
                    (new ProcessExecutionFault(), ex.Message);
            }
        }

        public bool DelSalesApp(SalesAppEntity salesApp)
        {
            if (salesApp == null)
                return false;
            List<SalesAppItemEntity> appItems = DataAccess.Select<SalesAppItemEntity>(new SalesAppItemEntity { AppId = salesApp.Id });
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    if (appItems != null)
                    {
                        DataAccess.Delete(appItems, broker);
                    }
                    DataAccess.Delete(salesApp, broker);
                    broker.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }
        }

        public bool SaveSalesApp(SalesAppSaveParam param)
        {
            if (param == null)
                return false;
            if (param.app == null)
                return false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    SalesAppEntity cur = DataAccess.SelectSingle<SalesAppEntity>(new SalesAppEntity { Id = param.app.Id });
                    if (cur == null)//新增
                    {
                        param.app.CreatedDate = DateTime.Now;
                        param.app.CreatedBy = param.curUserNo;
                        param.app.UpdatedDate = DateTime.Now;
                        param.app.UpdatedBy = param.curUserNo;
                        param.app.Status = ESOL.Common.WfStatus.WFS10.ToString();
                        //param.app.AppNo = MstCodeRuleBs.GetCurrentOrderCode("SalesAppCode");
                        foreach (var item in param.addItems)
                        {
                            item.AppId = param.app.Id;
                            item.AppNo = param.app.AppNo;
                        }
                        broker.BeginTransaction();
                        DataAccess.Insert(param.app, broker);
                        DataAccess.Insert(param.addItems, broker);
                        DataAccess.Insert(param.rls, broker);
                        broker.Commit();
                        return true;
                    }
                    else//修改
                    {
                        param.app.UpdatedDate = DateTime.Now;
                        param.app.UpdatedBy = param.curUserNo;
                        foreach (var item in param.addItems)
                        {
                            item.AppId = param.app.Id;
                            item.AppNo = param.app.AppNo;
                        }
                        broker.BeginTransaction();
                        DataAccess.Update(param.app, broker);
                        DataAccess.Delete(param.delItems, broker);
                        DataAccess.Update(param.updatedItems, broker);
                        DataAccess.Insert(param.addItems, broker);

                        DataAccess.Delete(param.rls, broker);
                        DataAccess.Insert(param.rls, broker);
                        broker.Commit();
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }
        }

        public List<SalesSoItemEntity> GetListSoItemBySoNo(string salesOrderNo)
        {
            try
            {
                if (string.IsNullOrEmpty(salesOrderNo))
                    return null;
                return DataAccess.Select<SalesSoItemEntity>(new SalesSoItemEntity { SoNo = salesOrderNo });
            }
            catch (Exception ex)
            {
                throw new FaultException<ProcessExecutionFault>
                    (new ProcessExecutionFault(), ex.Message);
            }
        }

        public List<BptAppEntity> SalesAppToBptApp(SalesAppEntity salesApp, DataSet salesAppItems)
        {
            if (salesApp == null || salesAppItems == null)
                return null;

            List<BptAppEntity> bptApps = new List<BptAppEntity>();
            foreach (DataRow dr in salesAppItems.Tables[0].Rows)
            {
                string code = MstCodeRuleBs.GetCurrentOrderCode("BptApplicationCode");
                if (code == null)
                    return null;
                BptAppEntity bptApp = new BptAppEntity()
                {
                    Id = Guid.NewGuid().ToString(),
                    IsActive = 1,
                    CreatedDate = DateTime.Now,
                    CreatedBy = salesApp.UpdatedBy,
                    UpdatedDate = DateTime.Now,
                    UpdatedBy = salesApp.UpdatedBy,
                    AppCode = code,
                    RequiredDate = (DateTime)dr["REQUIRED_DELIVERY_DATE"],
                    SalesAppId = salesApp.Id,
                    SalesAppItemId = dr["APPLICATION_ITEM_ID"].ToString(),
                    Material = dr["MATERIAL"].ToString(),
                    ContractModel = dr["PRODUCT_SPEC_OUT"].ToString(),
                    Status = Common.WfStatus.WFS10.ToString()
                };
                bptApps.Add(bptApp);
            }
            if (BptAppBs.InsertBptApp(bptApps))
                return bptApps;
            else
                return null;
        }

        //SalesAppItem
        public SalesAppItemEntity GetSalesAppItemByKey(string id)
        {
            if (string.IsNullOrEmpty(id))
                return null;
            return DataAccess.SelectSingle<SalesAppItemEntity>(new SalesAppItemEntity { Id = id });
        }

        public List<SalesAppItemEntity> GetSalesAppItemAllListByAppId(string salesAppId)
        {
            if (string.IsNullOrEmpty(salesAppId))
                return new List<SalesAppItemEntity>();
            return DataAccess.Select<SalesAppItemEntity>(new SalesAppItemEntity { AppId = salesAppId });
        }

        //SalesProdAppItem 生产计划申请单明细
        public SalesProdAppItemPageResult GetSalesProdAppItemAllListByCondition(SalesAppQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.CustomerId))
            {
                condition += " and app.CUSTOMER_ID='" + con.CustomerId + "'";
            }
            else if (!string.IsNullOrEmpty(con.CustomerName))
            {
                condition += " and app.CUSTOMER_NAME like '%" + con.CustomerName + "%'";
            }
            if (!string.IsNullOrEmpty(con.SoNo))
            {
                condition += " and app.SO_NO='" + con.SoNo + "'";
            }
            if (!string.IsNullOrEmpty(con.AppPerson))
            {
                condition += " and app.APP_PERSON='" + con.AppPerson + "'";
            }
            if (!string.IsNullOrEmpty(con.AppNo))
            {
                condition += " and app.APP_NO='" + con.AppNo + "'";
            }
            if (!string.IsNullOrEmpty(con.PaymentType))
            {
                condition += " and app.PAYMENT_TYPE='" + con.PaymentType + "'";
            }
            if (!string.IsNullOrEmpty(con.TranType))
            {
                condition += " and app.DELIVERY_TYPE='" + con.DeliveryType + "'";
            }
            if (con.Status != null)
            {
                condition += " and app.STATUS='" + con.Status + "'";
            }
            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from SALES_APP_ITEM) as cnt
from SALES_APP_ITEM 
where id not in (
    select top ({1}*({2}-1)) id from SALES_APP_ITEM)";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select appi.*,app.CUSTOMER_ID,app.CUSTOMER_NAME
from ({0}) appi
left join SALES_APP app on appi.APP_ID=app.ID";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t )";
                string sqlSource = @"select appi.*,app.CUSTOMER_ID,app.CUSTOMER_NAME
from SALES_APP_ITEM appi
left join SALES_APP app on appi.APP_ID=app.ID
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            SalesProdAppItemPageResult result = new SalesProdAppItemPageResult();
            if (ds.Tables[0].Rows.Count == 0)
            {
                result.ProdAppItems = null;
                result.TotalCount = 0;
                return result;
            }
            List<SalesProdAppItem> appItems = new List<SalesProdAppItem>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                SalesAppItemEntity appItem = new SalesAppItemEntity(dr);
                Mapper.CreateMap<SalesAppItemEntity, SalesProdAppItem>().ForMember(d => d.SelectedFlag, s => s.Ignore());
                var appItemDto = AutoMapper.Mapper.Map<SalesAppItemEntity, SalesProdAppItem>(appItem);
                if (dr["CUSTOMER_ID"] != null)
                {
                    appItemDto.CustomerId = dr["CUSTOMER_ID"].ToString();
                }
                if (dr["CUSTOMER_NAME"] != null)
                {
                    appItemDto.CustomerName = dr["CUSTOMER_NAME"].ToString();
                }
                appItems.Add(appItemDto);
            }
            result.ProdAppItems = appItems;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;
        }

        //SalesQu 销售报价单
        public SalesQuEntity GetSalesQuByKey(string quId)
        {
            if (string.IsNullOrEmpty(quId))
                return null;
            return DataAccess.SelectSingle<SalesQuEntity>(new SalesQuEntity { Id = quId });

        }

        public bool SaveSalesQu(SalesQuSaveParam param)
        {
            if (param == null)
                return false;
            if (param.qu == null)
                return false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    SalesQuEntity cur = DataAccess.SelectSingle<SalesQuEntity>(new SalesQuEntity { Id = param.qu.Id });
                    if (cur == null)//新增
                    {
                        param.qu.CreatedDate = DateTime.Now;
                        param.qu.UpdatedDate = DateTime.Now;
                        param.qu.CreatedBy = param.curUserNo;
                        param.qu.UpdatedBy = param.curUserNo;
                        param.qu.QuoteNo = MstCodeRuleBs.GetCurrentOrderCode("SalesQuoteOrder");
                        foreach (var item in param.addItems)
                        {
                            item.QuNo = param.qu.QuoteNo;
                            item.QuId = param.qu.Id;
                        }
                        broker.BeginTransaction();
                        DataAccess.Insert(param.qu, broker);
                        DataAccess.Insert(param.addItems, broker);
                        broker.Commit();
                        return true;
                    }
                    else//修改
                    {
                        param.qu.UpdatedDate = DateTime.Now;
                        param.qu.UpdatedBy = param.curUserNo;
                        foreach (var item in param.addItems)
                        {
                            item.QuNo = param.qu.QuoteNo;
                            item.QuId = param.qu.Id;
                        }
                        broker.BeginTransaction();
                        DataAccess.Update(param.qu, broker);
                        DataAccess.Delete(param.delItems, broker);
                        DataAccess.Update(param.updatedItems, broker);
                        DataAccess.Insert(param.addItems, broker);
                        broker.Commit();
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }
        }

        public SalesQuPageResult GetSalesQuAllListByCondition(SalesQuQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.QuNo))
            {
                condition += " and qu.QUOTE_NO='" + con.QuNo + "'";
            }
            if (!string.IsNullOrEmpty(con.CustomerId))
            {
                condition += " and qu.CUSTOMER_ID='" + con.CustomerId + "'";
            }
            else if (!string.IsNullOrEmpty(con.CustomerName))
            {
                condition += " and qu.CUSTOMER_NAME like '%" + con.CustomerName + "%'";
            }
            if (!string.IsNullOrEmpty(con.CustomerContact))
            {
                condition += " and qu.CONTACT_USER='" + con.CustomerContact + "'";
            }
            if (con.QuDate != null && con.QuDate != new DateTime(1, 1, 1))
            {
                condition += " and qu.QUOTE_DATE>='" + con.QuDate.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (con.QuDateTo != null && con.QuDateTo != new DateTime(1, 1, 1))
            {
                condition += " and qu.QUOTE_DATE<='" + con.QuDateTo.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (!string.IsNullOrEmpty(con.QuUser))
            {
                condition += " and qu.QUOTE_USER='" + con.QuUser + "'";
            }
            if (con.Status != null)
            {
                condition += " and qu.STATUS='" + con.Status + "'";
            }
            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from SALES_QU) as cnt
from SALES_QU
where id not in (
    select top ({1}*({2}-1)) id from SALES_QU order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select qu.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from ({0}) qu
left join BPMDB.dbo.PW_User u1 on qu.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on qu.CREATED_BY=u2.userNo";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select qu.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from SALES_QU qu
left join BPMDB.dbo.PW_User u1 on qu.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on qu.CREATED_BY=u2.userNo
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            SalesQuPageResult result = new SalesQuPageResult();
            if (ds.Tables[0].Rows.Count == 0)
            {
                result.QuOrders = null;
                result.TotalCount = 0;
                return result;
            }
            List<SalesQuEntity> qus = new List<SalesQuEntity>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                SalesQuEntity qu = new SalesQuEntity(dr);
                qus.Add(qu);
            }
            result.QuOrders = qus;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;

        }

        public bool InsertSalesQu(string userNo, SalesQuEntity qu)
        {
            if (qu == null)
                return false;
            qu.CreatedBy = userNo;
            qu.CreatedDate = DateTime.Now;
            return DataAccess.Insert(qu);
        }

        public bool UpdateSalesQu(string userNo, SalesQuEntity qu)
        {
            if (qu == null)
                return false;
            qu.UpdatedBy = userNo;
            qu.UpdatedDate = DateTime.Now;
            return DataAccess.Update(qu);
        }

        public bool DelSalesQu(SalesQuEntity qu)
        {
            if (qu == null)
                return false;
            List<SalesQuItemEntity> quItems = DataAccess.Select<SalesQuItemEntity>(new SalesQuItemEntity { QuId = qu.Id });
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    if (quItems != null)
                    {

                        DataAccess.Delete(quItems, broker);
                    }
                    DataAccess.Delete(qu, broker);
                    broker.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }

        }

        public List<SalesQuItemEntity> GetSalesQuItemAllListByQuId(string quId)
        {
            if (string.IsNullOrEmpty(quId))
                return null;
            return DataAccess.Select<SalesQuItemEntity>(new SalesQuItemEntity { QuId = quId });
        }

        //SalesShip 销售发货单
        public SalesShipEntity GetSalesShipByKey(string shipId)
        {
            if (string.IsNullOrEmpty(shipId))
                return null;
            return DataAccess.SelectSingle<SalesShipEntity>(new SalesShipEntity { Id = shipId });
        }

        public bool SaveSalesShip(SalesShipSaveParam param)
        {
            if (param == null)
                return false;
            if (param.ship == null)
                return false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    SalesShipEntity cur = DataAccess.SelectSingle<SalesShipEntity>(new SalesShipEntity { Id = param.ship.Id });
                    if (cur == null)//新增
                    {
                        param.ship.CreatedDate = DateTime.Now;
                        param.ship.UpdatedDate = DateTime.Now;
                        param.ship.CreatedBy = param.curUserNo;
                        param.ship.UpdatedBy = param.curUserNo;
                        param.ship.ShipNo = MstCodeRuleBs.GetCurrentOrderCode("SalesShip");
                        foreach (var item in param.addItems)
                        {
                            item.ShipNo = param.ship.ShipNo;
                            item.ShipId = param.ship.Id;
                        }
                        DataAccess.Insert(param.ship, broker);
                        DataAccess.Insert(param.addItems, broker);
                        broker.Commit();
                        return true;
                    }
                    else//修改
                    {
                        param.ship.UpdatedDate = DateTime.Now;
                        param.ship.UpdatedBy = param.curUserNo;
                        foreach (var item in param.addItems)
                        {
                            item.ShipNo = param.ship.ShipNo;
                            item.ShipId = param.ship.Id;
                        }
                        DataAccess.Update(param.ship, broker);
                        DataAccess.Delete(param.delItems, broker);
                        DataAccess.Update(param.updatedItems, broker);
                        DataAccess.Insert(param.addItems, broker);
                        broker.Commit();
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }
        }

        public SalesShipPageResult GetSalesShipAllListByCondition(SalesShipQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.ShipNo))
            {
                condition += " and ship.SHIP_NO='" + con.ShipNo + "'";
            }
            if (!string.IsNullOrEmpty(con.AppId))
            {
                condition += " and ship.SALES_APP_ID='" + con.AppId + "'";
            }
            else if (!string.IsNullOrEmpty(con.AppNo))
            {
                condition += " and ship.SALES_APP_NO like '%" + con.AppNo + "%'";
            }
            if (!string.IsNullOrEmpty(con.Type))
            {
                condition += " and ship.TYPE='" + con.Type + "'";
            }
            if (con.ShipDate != null && con.ShipDate != new DateTime(1, 1, 1))
            {
                condition += " and ship.SHIP_DATE>='" + con.ShipDate.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (con.ShipDateTo != null && con.ShipDateTo != new DateTime(1, 1, 1))
            {
                condition += " and ship.SHIP_DATE<='" + con.ShipDateTo.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (!string.IsNullOrEmpty(con.ShipPerson))
            {
                condition += " and ship.SHIP_PERSON='" + con.ShipPerson + "'";
            }
            if (!string.IsNullOrEmpty(con.ShipPersonName))
            {
                condition += " and ship.SHIP_PERSON_NAME='" + con.ShipPersonName + "'";
            }
            if (!string.IsNullOrEmpty(con.CustomerId))
            {
                condition += " and ship.CUSTOMER_ID='" + con.CustomerId + "'";
            }
            else if (!string.IsNullOrEmpty(con.CustomerName))
            {
                condition += " and ship.CUSTOMER_NAME like '%" + con.CustomerName + "%'";
            }
            if (!string.IsNullOrEmpty(con.TransportType))
            {
                condition += " and ship.TRANSPORT_TYPE='" + con.TransportType + "'";
            }
            if (!string.IsNullOrEmpty(con.ContractModel))
            {
                condition += " and item.CONTRACT_MODEL='"+con.ContractModel+"'";
            }
            condition += " and ship.IS_ACTIVE=" + con.IsActive;
            if (con.Status != null)
            {
                condition += " and ship.STATUS='" + con.Status + "'";
            }
            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from SALES_SHIP) as cnt
from SALES_SHIP
where id not in (
    select top ({1}*({2}-1)) id from SALES_SHIP order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select ship.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from ({0}) ship
left join BPMDB.dbo.PW_User u1 on ship.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on ship.CREATED_BY=u2.userNo";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select distinct ship.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from SALES_SHIP ship
left join BPMDB.dbo.PW_User u1 on ship.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on ship.CREATED_BY=u2.userNo
left join SALES_SHIP_ITEM item on ship.ID=item.SHIP_ID
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            SalesShipPageResult result = new SalesShipPageResult();
            if (ds.Tables[0].Rows.Count == 0)
            {
                result.Shipes = null;
                result.TotalCount = 0;
                return result;
            }
            List<SalesShipEntity> shipes = new List<SalesShipEntity>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                SalesShipEntity ship = new SalesShipEntity(dr);
                shipes.Add(ship);
            }
            result.Shipes = shipes;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;

        }

        public bool InsertSalesShip(string userNo, SalesShipEntity ship)
        {
            if (string.IsNullOrEmpty(userNo) || ship == null)
                return false;
            ship.CreatedBy = userNo;
            ship.CreatedDate = DateTime.Now;
            return DataAccess.Insert(ship);
        }

        public bool UpdateSalesShip(string userNo, SalesShipEntity ship)
        {
            if (string.IsNullOrEmpty(userNo) || ship == null)
                return false;
            ship.UpdatedBy = userNo;
            ship.UpdatedDate = DateTime.Now;
            return DataAccess.Update(ship);
        }

        public bool DelSalesShip(SalesShipEntity ship)
        {
            if (ship == null)
                return false;
            List<SalesShipItemEntity> shipItems = DataAccess.Select<SalesShipItemEntity>(new SalesShipItemEntity { ShipId = ship.Id });
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    if (shipItems != null)
                    {

                        DataAccess.Delete(shipItems, broker);
                    }
                    DataAccess.Delete(ship, broker);
                    broker.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }
        }

        //SalesShipItem 销售发货单明细
        public List<SalesShipItemEntity> GetSalesShipItemAllListByShipId(string shipId)
        {
            if (string.IsNullOrEmpty(shipId))
                return null;
            return DataAccess.Select<SalesShipItemEntity>(new SalesShipItemEntity { ShipId = shipId });
        }

        //SalesRo 销售退货单
        public SalesRoEntity GetSalesRoByKey(string roId)
        {
            if (string.IsNullOrEmpty(roId))
                return null;
            return DataAccess.SelectSingle<SalesRoEntity>(new SalesRoEntity { Id = roId });
        }

        public bool SaveSalesRo(SalesRoSaveParam param)
        {
            if (param == null)
                return false;
            if (param.ro == null)
                return false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    SalesRoEntity cur = DataAccess.SelectSingle<SalesRoEntity>(new SalesRoEntity { Id = param.ro.Id });
                    if (cur == null)//新增
                    {
                        param.ro.CreatedDate = DateTime.Now;
                        param.ro.UpdatedDate = DateTime.Now;
                        param.ro.CreatedBy = param.curUserNo;
                        param.ro.UpdatedBy = param.curUserNo;
                        param.ro.RoNo = MstCodeRuleBs.GetCurrentOrderCode("SalesReturnOrder");
                        foreach (var item in param.addItems)
                        {
                            item.RoNo = param.ro.RoNo;
                            item.RoId = param.ro.Id;
                        }
                        List<SalesRoItemEntity> roItems = new List<SalesRoItemEntity>();
                        foreach (var addItemDto in param.addItems)
                        {
                            Mapper.CreateMap<SalesRoItemDto, SalesRoItemEntity>();
                            var roItem = AutoMapper.Mapper.Map<SalesRoItemDto, SalesRoItemEntity>(addItemDto);
                            roItems.Add(roItem);
                        }

                        broker.BeginTransaction();
                        DataAccess.Insert(param.ro, broker);
                        DataAccess.Insert(roItems, broker);
                        broker.Commit();
                        return true;
                    }
                    else//修改
                    {
                        param.ro.UpdatedDate = DateTime.Now;
                        param.ro.UpdatedBy = param.curUserNo;
                        foreach (var item in param.addItems)
                        {
                            item.RoNo = param.ro.RoNo;
                            item.RoId = param.ro.Id;
                        }
                        List<SalesRoItemEntity> delItems = new List<SalesRoItemEntity>();
                        foreach (var delItemDto in param.delItems)
                        {
                            Mapper.CreateMap<SalesRoItemDto, SalesRoItemEntity>();
                            var roItem = AutoMapper.Mapper.Map<SalesRoItemDto, SalesRoItemEntity>(delItemDto);
                            delItems.Add(roItem);
                        }
                        List<SalesRoItemEntity> updatedItems = new List<SalesRoItemEntity>();
                        foreach (var updatedItemDto in param.updatedItems)
                        {
                            Mapper.CreateMap<SalesRoItemDto, SalesRoItemEntity>();
                            var roItem = AutoMapper.Mapper.Map<SalesRoItemDto, SalesRoItemEntity>(updatedItemDto);
                            updatedItems.Add(roItem);
                        }
                        List<SalesRoItemEntity> addItems = new List<SalesRoItemEntity>();
                        foreach (var addItemDto in param.addItems)
                        {
                            Mapper.CreateMap<SalesRoItemDto, SalesRoItemEntity>();
                            var roItem = AutoMapper.Mapper.Map<SalesRoItemDto, SalesRoItemEntity>(addItemDto);
                            addItems.Add(roItem);
                        }

                        broker.BeginTransaction();
                        DataAccess.Update(param.ro, broker);
                        DataAccess.Delete(delItems, broker);
                        DataAccess.Update(updatedItems, broker);
                        DataAccess.Insert(addItems, broker);
                        broker.Commit();
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }
        }

        public SalesRoPageResult GetSalesRoAllListByCondition(SalesRoQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.RoNo))
            {
                condition += " and ro.RO_NO='" + con.RoNo + "'";
            }
            if (!string.IsNullOrEmpty(con.SalesProdAppNo))
            {
                condition += " and ro.SALES_APP_NO='" + con.SalesProdAppNo + "'";
            }
            if (!string.IsNullOrEmpty(con.CustomerId))
            {
                condition += " and ro.CUSTOMER_ID='" + con.CustomerId + "'";
            }
            else if (!string.IsNullOrEmpty(con.CustomerName))
            {
                condition += " and ro.CUSTOMER_NAME like '%" + con.CustomerName + "%'";
            }
            condition += " and ro.IS_ACTIVE=" + con.IsActive;
            if (con.Status != null)
            {
                condition += " and ro.STATUS='" + con.Status + "'";
            }
            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from SALES_RO) as cnt
from SALES_RO
where id not in (
    select top ({1}*({2}-1)) id from SALES_RO order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select ro.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from ({0}) ro
left join BPMDB.dbo.PW_User u1 on ro.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on ro.CREATED_BY=u2.userNo";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select distinct ro.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from SALES_RO ro
left join SALES_RO_ITEM ritem on ro.ID=ritem.RO_ID
left join SALES_APP_ITEM aitem on ritem.SALES_APP_ITEM_ID=aitem.ID
left join BPMDB.dbo.PW_User u1 on ro.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on ro.CREATED_BY=u2.userNo
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            SalesRoPageResult result = new SalesRoPageResult();
            if (ds.Tables[0].Rows.Count == 0)
            {
                result.ReturnOrders = null;
                result.TotalCount = 0;
                return result;
            }
            List<SalesRoEntity> returnOrders = new List<SalesRoEntity>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                SalesRoEntity ro = new SalesRoEntity(dr);
                returnOrders.Add(ro);
            }
            result.ReturnOrders = returnOrders;
            result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            return result;

        }

        public bool InsertSalesRo(string userNo, SalesRoEntity ro)
        {
            if (string.IsNullOrEmpty(userNo) || ro == null)
                return false;
            ro.CreatedBy = userNo;
            ro.CreatedDate = DateTime.Now;
            return DataAccess.Insert(ro);
        }

        public bool UpdateSalesRo(string userNo, SalesRoEntity ro)
        {
            if (string.IsNullOrEmpty(userNo) || ro == null)
                return false;
            ro.CreatedBy = userNo;
            ro.CreatedDate = DateTime.Now;
            return DataAccess.Update(ro);
        }

        public bool DelSalesRo(SalesRoEntity ro)
        {
            if (ro == null)
                return false;
            List<SalesRoItemEntity> roItems = DataAccess.Select<SalesRoItemEntity>(new SalesRoItemEntity { RoId = ro.Id });
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    if (roItems != null)
                    {

                        DataAccess.Delete(roItems, broker);
                    }
                    DataAccess.Delete(ro, broker);
                    broker.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }
        }

        //SalesRoItem 销售退货单明细
        public List<SalesRoItemDto> GetSalesRoItemDtoAllListByRoId(string roId)
        {
            if (string.IsNullOrEmpty(roId))
                return null;
            var roItems = DataAccess.Select<SalesRoItemEntity>(new SalesRoItemEntity { RoId = roId });
            if (roItems != null)
            {
                List<SalesRoItemDto> roItemsDto = new List<SalesRoItemDto>();
                foreach (var roItem in roItems)
                {
                    Mapper.CreateMap<SalesRoItemEntity, SalesRoItemDto>();
                    var roItemDto = AutoMapper.Mapper.Map<SalesRoItemEntity, SalesRoItemDto>(roItem);
                    var shipItem = DataAccess.SelectSingle<SalesShipItemEntity>(new SalesShipItemEntity { MaterialId = roItem.MaterialId, AppItemId = roItem.SalesAppItemId });
                    var appItem=DataAccess.SelectSingle<SalesAppItemEntity>(new SalesAppItemEntity{Id=roItem.SalesAppItemId});
                    if (shipItem != null)
                    {
                        roItemDto.ShipedQty = shipItem.ShipedQty;
                    }
                    var feedback = DataAccess.SelectSingle<SalesShipFeedbackEntity>(new SalesShipFeedbackEntity { ShipItemId = roItem.Id });
                    if (feedback != null)
                    {
                        roItemDto.ArvQty = feedback.RevQty;
                    }
                    if (appItem != null)
                    {
                        roItemDto.ContractModel = appItem.CustomerModel;
                    }
                    roItemsDto.Add(roItemDto);
                }
                return roItemsDto;
            }
            else
            {
                return null;
            }
        }



        //销售汇总表
        public SalesCustomerSummeryPageResult GetSalesCustomerSummary(SalesCustomerSummaryQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.CustomerId))
            {
                condition += " and cus.ID='" + con.CustomerId + "'";
            }
            else if (!string.IsNullOrEmpty(con.CustomerName))
            {
                condition += " and cus.CUSTOMER_NAME like '%" + con.CustomerName + "%'";
            }
            if (!string.IsNullOrEmpty(con.CustomerType))
            {
                condition += " and cus.INDUSTRY_TYPE_CODE='" + con.CustomerType + "'";
            }
            if (!string.IsNullOrEmpty(con.CustomerLevel))
            {
                condition += " and cus.LEVEL='" + con.CustomerLevel + "'";
            }
            if (!string.IsNullOrEmpty(con.SalesmanNo))
            {
                condition += " and sal.SALESMAN_NO='"+con.SalesmanNo+"'";
            }
            if (con.SalesAppCreatedDate != null && con.SalesAppCreatedDate != new DateTime(1, 1, 1))
            {
                condition += " and app.CREATED_BY>='" + con.SalesAppCreatedDate.ToString("yyyy-MM-dd HH:mm:ss")+"'";
            }
            if (con.SalesAppCreatedDateTo != null && con.SalesAppCreatedDateTo != new DateTime(1, 1, 1))
            {
                condition += " and app.CREATED_BY<='" + con.SalesAppCreatedDateTo.ToString("yyyy-MM-dd HH:mm:ss")+"'";
            }

            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from MST_CUSTOMER) as cnt
from MST_CUSTOMER
where id not in (
    select top ({1}*({2}-1)) id from MST_CUSTOMER order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select cus.* from ({0}) cus";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select distinct cus.* from MST_CUSTOMER cus
left join dbo.MST_CUSTOMER_SALESMAN sal on cus.ID=sal.CUSTOMER_ID
left join SALES_APP app on cus.ID=app.CUSTOMER_ID
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            SalesCustomerSummeryPageResult result = new SalesCustomerSummeryPageResult();
            List<SalesCustomerSummaryDto> dtos = new List<SalesCustomerSummaryDto>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                SalesCustomerSummaryDto dto = new SalesCustomerSummaryDto();
                dto.CustomerId = dr["ID"].ToString();
                dto.CustomerName = dr["CUSTOMER_NAME"].ToString();
                dto.CustomerCode = dr["CUSTOMER_CODE"].ToString();
                dto.CustomerStatus = dr["STATUS"].ToString();
                var apps = SalesAppBs.GetSalesAppForCustomerSummary(con.CompanyId, con.SobId, dr["ID"].ToString(), con.SalesAppCreatedDate, con.SalesAppCreatedDateTo);
                if (apps != null && apps.Count > 0)
                {
                    dto.SalesAppCount = apps.Count;
                    dto.SalesAppList = apps.Select(a => a.Id).ToList();
                    //dto.SalesAppUnclosedList = apps.Where(a => a.Status != (Common.WfStatus.WFS30.ToString())).ToList().Select(b => b.Id).ToList();
                    // dto.SalesAppUnclosedCount = dto.SalesAppUnclosedList.Count;
                    //销售金额
                    dto.SalesAmount = apps.Sum(a => a.TotalAmount);
                    //发货金额
                    var shipes = SalesShipBs.GetSalesShipForCustomerSummary(dto.SalesAppList);
                    if (shipes != null)
                    {
                        dto.ShipedAmount = shipes.Sum(a => a.TotalAmount);
                    }
                    //退货金额
                    var ros = SalesRoBs.GetSalesRoForCustomerSummary(dto.SalesAppList);
                    if (ros != null)
                    {
                        dto.ReturnAmount = ros.Sum(a => a.TotalAmount);
                    }
                    //已收款金额

                    //已开票金额

                }

                var appUncloseds = SalesAppBs.GetSalesAppUnclosedForCustomerSummary(con.CompanyId, con.SobId, dr["ID"].ToString(), con.SalesAppCreatedDate, con.SalesAppCreatedDateTo);
                if (appUncloseds != null)
                {
                    dto.SalesAppUnclosedCount = appUncloseds.Count;
                    dto.SalesAppUnclosedList = appUncloseds.Select(a => a.Id).ToList();

                }
                dtos.Add(dto);
            }
            result.SummaryDto = dtos;
            if (ds.Tables[0].Rows.Count > 0)
            {
                result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
            }
            return result;
        }

        public List<SalesAppEntity> GetSalesAppAllListByAppIdList(List<string> appIdList)
        {
            try
            {
                if (appIdList == null)
                    return null;
                if (appIdList.Count == 0)
                    return null;
                string sql = @"select * from SALES_APP where ID in ({0})";
                string sql1 = "";
                foreach (string appId in appIdList)
                {
                    sql1 += "'" + appId + "',";
                }
                sql1 = sql1.Substring(0, sql1.Length - 1);
                sql = string.Format(sql, sql1);
                return DataAccess.Select<SalesAppEntity>(sql);
            }
            catch (Exception ex)
            {
                MyDebug.write(ex.Message);
                return null;
            }
        }

        public List<SalesProdAppFinishDialogDto> GetSalesProdAppFinishDialogDto(SalesAppEntity app)
        {
            if (app == null)
                return null;
            List<SalesAppItemEntity> appItems = DataAccess.Select<SalesAppItemEntity>(new SalesAppItemEntity { AppId = app.Id });
            if (appItems == null)
                return null;
            if (appItems.Count == 0)
                return null;
            List<SalesProdAppFinishDialogDto> dtos = new List<SalesProdAppFinishDialogDto>();
            foreach (var appItem in appItems)
            {
                SalesProdAppFinishDialogDto dto = new SalesProdAppFinishDialogDto();
                dto.AppItemId = appItem.Id;
                dto.AppId = appItem.AppId;
                dto.ModelContract = appItem.CustomerModel;
                dto.Material = appItem.Material;
                dto.ProductId = appItem.ProductId;
                dto.MaterialName = appItem.ProductName;
                dto.MaterialCode = appItem.ProductCode;
                dto.Qty = appItem.Qty;
                dto.DeliveryTime = appItem.ReqDeliveryDate;
                if (!string.IsNullOrEmpty(appItem.ProductId))
                {
                    DataSet ds = BptBlueprintBs.GetBlueprintByProductId(appItem.ProductId);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        List<BptBlueprintDto> bpsDto = new List<BptBlueprintDto>();
                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            BptBlueprintDto bpDto = new BptBlueprintDto();
                            bpDto.BlueprintId = dr["BLUEPRINT_ID"].ToString();
                            bpDto.ParentId = dr["PARENT_ID"].ToString();
                            bpDto.BptTaskId = dr["BPT_TASK_ID"].ToString();
                            bpDto.BptTaskCode = dr["TASK_CODE"].ToString();
                            bpDto.BlueprintType = dr["BLUEPRINT_TYPE"].ToString();
                            bpDto.FileName = dr["FILE_NAME"].ToString();
                            bpDto.UpdatedBy = dr["UPDATED_BY"].ToString();
                            bpDto.UpdatedDate = (DateTime)dr["UPDATED_DATE"];
                            bpDto.UpdatedUser = dr["UPDATED_USER"].ToString();
                            bpDto.Remark = dr["REMARK"].ToString();
                            bpsDto.Add(bpDto);
                        }
                        dto.BpsDto = bpsDto;
                        dto.BpCount = ds.Tables[0].Rows.Count;
                    }
                    else
                    {
                        dto.BpCount = 0;
                    }
                }
                dtos.Add(dto);
            }
            return dtos;

        }

        public List<SalesProdAppFinishResult> SalesProdAppFinish(string userNo, List<SalesProdAppFinishDialogDto> source)
        {
            List<SalesProdAppFinishResult> results = new List<SalesProdAppFinishResult>();
            List<BptAppEntity> addBptApps = new List<BptAppEntity>();
            List<ProdPlanOrderEntity> addProdPos = new List<ProdPlanOrderEntity>();
            foreach (var s in source)
            {
                SalesProdAppFinishResult r = new SalesProdAppFinishResult();
                r.AppItemId = s.AppItemId;
                if (s.Operation == (int)SalesProdAppFinishOperation.不操作)
                {
                    r.Operation = (int)SalesProdAppFinishOperation.不操作;
                }
                else if (s.Operation == (int)SalesProdAppFinishOperation.创建技术申请)
                {
                    r.Operation = (int)SalesProdAppFinishOperation.创建技术申请;

                    BptAppEntity bptApp = new BptAppEntity();
                    bptApp.Id = Guid.NewGuid().ToString();
                    bptApp.IsActive = 1;
                    bptApp.CreatedBy = userNo;
                    bptApp.CreatedDate = DateTime.Now;
                    bptApp.UpdatedBy = userNo;
                    bptApp.UpdatedDate = DateTime.Now;
                    bptApp.AppType = "DT01";
                    bptApp.AppCode = MstCodeRuleBs.GetCurrentOrderCode("BptApplicationCode");
                    bptApp.SalesAppItemId = s.AppItemId;
                    bptApp.SalesAppId = s.AppId;
                    bptApp.Material = s.Material;
                    bptApp.ContractModel = s.ModelContract;
                    bptApp.RequiredDate = s.BptAppReqTime;
                    bptApp.Status = WfStatus.WFS10.ToString();
                    addBptApps.Add(bptApp);

                    r.BptAppId = bptApp.Id;
                }
                else
                {
                    r.Operation = (int)SalesProdAppFinishOperation.创建生产计划单;
                    ProdPlanOrderEntity po = new ProdPlanOrderEntity();
                    po.Id = Guid.NewGuid().ToString();
                    po.IsActive = 1;
                    po.CreatedBy = userNo;
                    po.CreatedDate = DateTime.Now;
                    po.UpdatedBy = userNo;
                    po.UpdatedDate = DateTime.Now;
                    po.PoNo = MstCodeRuleBs.GetCurrentOrderCode("ProductPlanOrder");
                    po.SappItemId = s.AppItemId;
                    po.ContractModel = s.ModelContract;
                    po.ProductId = s.ProductId;
                    po.ProductName = s.MaterialName;
                    po.ProductCode = s.MaterialCode;
                    po.Qty = s.Qty;
                    po.DeliveryDate = s.DeliveryTime;
                    po.PoUser = userNo;
                    po.PoDate = DateTime.Now;
                    po.DeliveryDate = s.ProdPoReqTime;
                    po.Status = "PSTATUS01";
                    addProdPos.Add(po);

                    r.ProdPoId = po.Id;
                }
                results.Add(r);
            }
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    DataAccess.Insert(addBptApps, broker);
                    DataAccess.Insert(addProdPos, broker);
                    broker.Commit();
                    return results;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return null;
                }
            }

        }

        //销售业务清单
        public SalesCutomerBusinessListPageResult GetSalesCustomerBusinessList(SalesCutomerBusinessListQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;

            string condition = "";
            if (!string.IsNullOrEmpty(con.CustomerNo))
            {
                condition += " and app.CUSTOMER_CODE='" + con.CustomerNo + "'";
            }
            if (!string.IsNullOrEmpty(con.ContractModel))
            {
                condition += " and item.CUSTOMER_MODEL='" + con.ContractModel + "'";
            }
            if (!string.IsNullOrEmpty(con.ItemType))
            {
                condition += " and item.ITEM_TYPE='" + con.ItemType + "'";
            }
            if (!string.IsNullOrEmpty(con.SalesAppNo))
            {
                condition += " and item.APP_NO='" + con.SalesAppNo + "'";
            }
            if (!string.IsNullOrEmpty(con.ItemCode))
            {
                condition += " and item.PRODUCT_CODE='" + con.ItemCode + "'";
            }
            if (!string.IsNullOrEmpty(con.ProductType))
            {
                condition += " and item.PRODUCT_CODE='" + con.ProductType + "'";
            }
            if (con.AppDate != null && con.AppDate != new DateTime(1, 1, 1))
            {
                condition += " and app.CREATED_DATE>='" + con.AppDate.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (con.AppDateTo != null && con.AppDateTo != new DateTime(1, 1, 1))
            {
                condition += " and app.CREATED_DATE<='" + con.AppDateTo.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (con.DeliveryDate != null && con.DeliveryDate != new DateTime(1, 1, 1))
            {
                condition += " and app.REQ_DELIVERY_DATE>='" + con.DeliveryDate.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }
            if (con.DeliveryDateTo != null && con.DeliveryDateTo != new DateTime(1, 1, 1))
            {
                condition += " and app.REQ_DELIVERY_DATE<='" + con.DeliveryDateTo.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            }

            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from SALES_RO) as cnt
from SALES_APP_ITEM
where id not in (
    select top ({1}*({2}-1)) id from SALES_APP_ITEM)";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select so.SO_NO as SALES_SO_NO,app.CREATED_DATE as SALES_APP_DATE,item.*
from ({0}) item
left join SALES_APP app on item.APP_ID=app.ID
left join SALES_RL_SO_APP rl on item.ID=rl.APP_ITEM_ID and app.ID=rl.APP_ID
left join SALES_SO so on rl.SO_ID=so.ID";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t)";
                string sqlSource = @"select so.SO_NO as SALES_SO_NO,app.CREATED_DATE as SALES_APP_DATE,item.*
from SALES_APP_ITEM item
left join SALES_APP app on item.APP_ID=app.ID
left join SALES_RL_SO_APP rl on item.ID=rl.APP_ITEM_ID and app.ID=rl.APP_ID
left join SALES_SO so on rl.SO_ID=so.ID where 1=1";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;

            SalesCutomerBusinessListPageResult dtos = new SalesCutomerBusinessListPageResult();
            List<SalesCutomerBusinessListDto> list = new List<SalesCutomerBusinessListDto>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                SalesAppItemEntity appItem = new SalesAppItemEntity(dr);
                Mapper.CreateMap<SalesAppItemEntity, SalesCutomerBusinessListDto>();
                SalesCutomerBusinessListDto dto = AutoMapper.Mapper.Map<SalesAppItemEntity, SalesCutomerBusinessListDto>(appItem);
                dto.SelectedFlag = 0;
                if (dr["SALES_SO_NO"] != DBNull.Value)
                    dto.SalesSoNoStr = dr["SALES_SO_NO"].ToString();
                if (dr["SALES_APP_DATE"] != DBNull.Value)
                    dto.SalesAppDate = (DateTime)dr["SALES_APP_DATE"];

                var shipItems=DataAccess.Select<SalesShipItemEntity>(new SalesShipItemEntity { AppItemId = appItem.Id });
                dto.ShipedQty = shipItems.Sum(a => a.ShipedQty);
                dto.UnshipedQty = appItem.Qty - dto.ShipedQty;

                var appBindSoItems = DataAccess.Select<SalesRlSoAppEntity>(new SalesRlSoAppEntity { AppItemId = appItem.Id });
                dto.BindedContractQty = appBindSoItems.Sum(a => a.SoItemQty);
                dto.UnbindedContractQty = appItem.Qty - dto.BindedContractQty;

                string sqlRo = @"select item.* from dbo.SALES_RO_ITEM item left join SALES_RO ro on item.RO_ID=ro.ID 
where ro.STATUS='WFS30'";
                DataSet dsRo = DataAccess.SelectDataSet(sqlRo);
                List<SalesRoItemEntity> roItems=new List<SalesRoItemEntity>();
                if (dsRo != null)
                {
                    foreach(DataRow drRo in dsRo.Tables[0].Rows)
                    {
                        SalesRoItemEntity roItem=new SalesRoItemEntity(drRo);
                        roItems.Add(roItem);
                    }
                    dto.ReturnedQty = roItems.Sum(a => a.ReturnQty);
                    dto.ReturnAmount = roItems.Sum(a => a.SubTotal);
                }

                list.Add(dto);

            }
            dtos.TotalCount = list.Count;
            dtos.BusinessListDto = list;
            return dtos;
        }

        //查询已绑定的合同单号
        public List<SalesAppBindSoDto> GetSalesBindByAppItemId(string appItemId)
        {
            if (string.IsNullOrEmpty(appItemId))
            {
                return null;
            }
            return DataAccess.Select<SalesAppBindSoDto>(new SalesAppBindSoDto { AppItemId = appItemId });
        }

        //业务申请单绑定合同单号
        public SalesCutomerBusinessListDto BindingSoForCustomerBusiness(SalesCutomerBusinessListDto d, List<SalesAppBindSoDto> s)
        {
            string soNoList = "";
            List<SalesRlSoAppEntity> addBindList = new List<SalesRlSoAppEntity>();
            List<SalesRlSoAppEntity> delBindList = new List<SalesRlSoAppEntity>();
            delBindList = DataAccess.Select<SalesRlSoAppEntity>(new SalesRlSoAppEntity { AppItemId = d.Id });
            foreach (var dto in s)
            {
                d.SalesSoNo.Add(dto.SoNo);
                soNoList += dto.SoNo + ";";

                Mapper.CreateMap<SalesAppBindSoDto, SalesRlSoAppEntity>();
                SalesRlSoAppEntity rl = AutoMapper.Mapper.Map<SalesAppBindSoDto, SalesRlSoAppEntity>(dto);
                addBindList.Add(rl);
            }

            SalesAppItemEntity appItem = SalesAppItemBs.GetSalesAppItem(new SalesAppItemEntity { Id = d.Id });
            if (appItem != null)
            {
                appItem.AppNo += soNoList;
            }

            SalesAppEntity updatedApp = SalesAppBs.GetSalesApp(new SalesAppEntity { Id = d.AppId });
            updatedApp.SoNo += soNoList;

            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    DataAccess.Update(updatedApp, broker);
                    DataAccess.Update(appItem, broker);
                    DataAccess.Delete(delBindList, broker);
                    DataAccess.Insert(addBindList, broker);
                    broker.Commit();
                    return d;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return null;
                }
            }

        }

    }
}

